﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 用户DAL
    /// </summary>
    public  class PrvUser
    {


        /// <summary>
        /// 是否存在该记录
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fLogID">用户登录号</param>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <returns>是否存在</returns>
        public bool Exists(string fLogID, string fEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_PrvUser");
            strSql.Append(" where FLogID=@FLogID and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fLogID;
            parameters[1].Value = fEntityNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fLogID">用户登录号</param>
        /// <param name="fEntityNo">组织机构代码</param>
        /// <returns>用户信息</returns>
        public Model.PrvUser GetModel(string fLogID, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FLogID,FEntityNo,FUserName,Fpassword,FLastLoginTime,FLastLoginIPAddr,FLoginCnt,FProhibitLogin,FForeverAvailable,FAvailableDate,FEmplNo,FSSOUid,FIsLookAllDaily,FIsInstruct from t_PrvUser ");
            strSql.Append(" where FLogID=@FLogID and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fLogID;
            parameters[1].Value = fEntityNo;

            Model.PrvUser model = new Model.PrvUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FLogID"] != null && ds.Tables[0].Rows[0]["FLogID"].ToString() != "")
                {
                    model.FLogID = ds.Tables[0].Rows[0]["FLogID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FEntityNo"] != null && ds.Tables[0].Rows[0]["FEntityNo"].ToString() != "")
                {
                    model.FEntityNo = ds.Tables[0].Rows[0]["FEntityNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FUserName"] != null && ds.Tables[0].Rows[0]["FUserName"].ToString() != "")
                {
                    model.FUserName = ds.Tables[0].Rows[0]["FUserName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Fpassword"] != null && ds.Tables[0].Rows[0]["Fpassword"].ToString() != "")
                {
                    model.Fpassword = ds.Tables[0].Rows[0]["Fpassword"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FLastLoginTime"] != null && ds.Tables[0].Rows[0]["FLastLoginTime"].ToString() != "")
                {
                    model.FLastLoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["FLastLoginTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FLastLoginIPAddr"] != null && ds.Tables[0].Rows[0]["FLastLoginIPAddr"].ToString() != "")
                {
                    model.FLastLoginIPAddr = ds.Tables[0].Rows[0]["FLastLoginIPAddr"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FLoginCnt"] != null && ds.Tables[0].Rows[0]["FLoginCnt"].ToString() != "")
                {
                    model.FLoginCnt = int.Parse(ds.Tables[0].Rows[0]["FLoginCnt"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FProhibitLogin"] != null && ds.Tables[0].Rows[0]["FProhibitLogin"].ToString() != "")
                {
                    model.FProhibitLogin = int.Parse(ds.Tables[0].Rows[0]["FProhibitLogin"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FForeverAvailable"] != null && ds.Tables[0].Rows[0]["FForeverAvailable"].ToString() != "")
                {
                    model.FForeverAvailable = int.Parse(ds.Tables[0].Rows[0]["FForeverAvailable"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FAvailableDate"] != null && ds.Tables[0].Rows[0]["FAvailableDate"].ToString() != "")
                {
                    model.FAvailableDate = DateTime.Parse(ds.Tables[0].Rows[0]["FAvailableDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FEmplNo"] != null && ds.Tables[0].Rows[0]["FEmplNo"].ToString() != "")
                {
                    model.FEmplNo = ds.Tables[0].Rows[0]["FEmplNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FSSOUid"] != null && ds.Tables[0].Rows[0]["FSSOUid"].ToString() != "")
                {
                    model.FSSOUid = ds.Tables[0].Rows[0]["FSSOUid"].ToString();
                }
                if (ds.Tables[0].Columns.Contains("FIsLookAllDaily"))
                {
                    model.FIsLookAllDaily = ds.Tables[0].Rows[0]["FIsLookAllDaily"].ToString();
                }
                if (ds.Tables[0].Columns.Contains("FIsInstruct"))
                {
                    if (ds.Tables[0].Rows[0]["FIsInstruct"] != null && ds.Tables[0].Rows[0]["FIsInstruct"].ToString() != "")
                    {
                        model.FIsInstruct = ds.Tables[0].Rows[0]["FIsInstruct"].ToString();
                    }
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 密码重置
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fLogID">用户登录号</param>
        /// <param name="fpassword">用户密码</param>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <returns>是否重置成功</returns>
        public bool ResetPwd(string fLogID, string fpassword, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" update t_PrvUser set Fpassword=@Fpassword ");
            strSql.Append(" where FLogID=@FLogID and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
                    new SqlParameter("@Fpassword", SqlDbType.NVarChar,255)};
            parameters[0].Value = fLogID;
            parameters[1].Value = fEntityNo;
            parameters[2].Value = fpassword;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }



        /// <summary>
        /// 删除一条数据
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fLogID">用户登录号</param>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <returns>是否删除成功</returns>
        public bool Delete(string fLogID, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("delete from t_PrvUser ");
            strSql.Append(" where FLogID=@FLogID and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fLogID;
            parameters[1].Value = fEntityNo;

            //删除用户隶属组信息
            StringBuilder strSql2 = new StringBuilder();
            cmds = new CommandInfo();
            strSql2.Append("DELETE FROM t_PrvUserGroup WHERE FEntityNo=@FEntityNo and FLogID=@FLogID");
            SqlParameter[] parameters1 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12)
                                        };
            parameters1[0].Value = fLogID;
            parameters1[1].Value = fEntityNo;
            cmds.CommandText = strSql2.ToString();
            cmds.Parameters = parameters1;
            cmdList.Add(cmds);
            //删除目录用户组信息
            strSql2 = new StringBuilder();
            cmds = new CommandInfo();
            strSql2.Append("DELETE FROM t_DirPrvUserGroup WHERE FEntityNo=@FEntityNo and FLogID=@FLogID");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12)
                                        };
            parameters2[0].Value = fLogID;
            parameters2[1].Value = fEntityNo;
            cmds.CommandText = strSql2.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);
            //删除文档目录权限 accManagerRoleDirAuth
            strSql2 = new StringBuilder();
            strSql2.Append("delete from t_accManagerRoleDirAuth ");
            strSql2.Append(" where  FEntityNo=@FEntityNo and FLogID=@FLogID ");
            SqlParameter[] parameters3 = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FLogID", SqlDbType.NVarChar,50)};
            parameters3[0].Value = fEntityNo;
            parameters3[1].Value = fLogID;
            cmds.CommandText = strSql2.ToString();
            cmds.Parameters = parameters3;
            cmdList.Add(cmds);
            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
            }
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 增加用户
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="userModel">用户信息</param>
        /// <param name="userGroupList">用户组信息</param>
        public void AddPrvUser(Model.PrvUser userModel, List<string> userGroupList)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("insert into t_PrvUser(");
            strSql.Append("FLogID,FEntityNo,FUserName,Fpassword,FLastLoginTime,FLastLoginIPAddr,FLoginCnt,FProhibitLogin,FForeverAvailable,FAvailableDate,FEmplNo,FSSOUid,FIsLookAllDaily,FIsInstruct)");
            strSql.Append(" values (");
            strSql.Append("@FLogID,@FEntityNo,@FUserName,@Fpassword,@FLastLoginTime,@FLastLoginIPAddr,@FLoginCnt,@FProhibitLogin,@FForeverAvailable,@FAvailableDate,@FEmplNo,@FSSOUid,@FIsLookAllDaily,@FIsInstruct)");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@Fpassword", SqlDbType.NVarChar,255),
					new SqlParameter("@FLastLoginTime", SqlDbType.DateTime),
					new SqlParameter("@FLastLoginIPAddr", SqlDbType.NVarChar,15),
					new SqlParameter("@FLoginCnt", SqlDbType.Int,4),
					new SqlParameter("@FProhibitLogin", SqlDbType.Int,4),
					new SqlParameter("@FForeverAvailable", SqlDbType.Int,4),
					new SqlParameter("@FAvailableDate", SqlDbType.DateTime),
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FSSOUid", SqlDbType.NVarChar,50),
                    new SqlParameter("@FIsLookAllDaily",SqlDbType.NVarChar,1),
                    new SqlParameter("@FIsInstruct",SqlDbType.NVarChar,1)
                                        };
            parameters[0].Value = userModel.FLogID;
            parameters[1].Value = userModel.FEntityNo;
            parameters[2].Value = userModel.FUserName;
            parameters[3].Value = userModel.Fpassword;
            parameters[4].Value = userModel.FLastLoginTime;
            parameters[5].Value = userModel.FLastLoginIPAddr;
            parameters[6].Value = userModel.FLoginCnt;
            parameters[7].Value = userModel.FProhibitLogin;
            parameters[8].Value = userModel.FForeverAvailable;
            parameters[9].Value = userModel.FAvailableDate;
            parameters[10].Value = userModel.FEmplNo;
            parameters[11].Value = userModel.FSSOUid;
            parameters[12].Value = userModel.FIsLookAllDaily;
            parameters[13].Value = userModel.FIsInstruct;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);
            //增加用户隶属组信息
            if (userGroupList.Count > 0)
            {
                foreach (string gpID in userGroupList)
                {
                    strSql = new StringBuilder();
                    cmds = new CommandInfo();
                    strSql.Append("INSERT INTO t_PrvUserGroup (FGroupNo,FEntityNo,FLogID) VALUES (@FGroupNo,@FEntityNo,@FLogID)");
                    SqlParameter[] parameters1 = {
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,18),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)
                                              };
                    parameters1[0].Value = gpID;
                    parameters1[1].Value = userModel.FEntityNo;
                    parameters1[2].Value = userModel.FLogID;
                    cmds.CommandText = strSql.ToString();
                    cmds.Parameters = parameters1;
                    cmdList.Add(cmds);
                }
            }
            //增加用户隶属目录用户组和相关目录权限
            //if (dirUserList.Count > 0)
            //{
            //    foreach (string gpID in dirUserList)
            //    {
            //        strSql = new StringBuilder();
            //        cmds = new CommandInfo();
            //        strSql.Append("INSERT INTO t_DirPrvUserGroup (FGroupNo,FEntityNo,FLogID) VALUES (@FGroupNo,@FEntityNo,@FLogID)");
            //        SqlParameter[] parameters2 = {
            //        new SqlParameter("@FGroupNo", SqlDbType.NVarChar,18),
            //        new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
            //        new SqlParameter("@FLogID", SqlDbType.NVarChar,30)
            //                                  };
            //        parameters2[0].Value = gpID;
            //        parameters2[1].Value = userModel.FEntityNo;
            //        parameters2[2].Value = userModel.FLogID;
            //        cmds.CommandText = strSql.ToString();
            //        cmds.Parameters = parameters2;
            //        cmdList.Add(cmds);
            //    }


            //    //增加用户目录权限,根据用户所属的目录用户组
            //    strSql = new StringBuilder();

            //    strSql.Append("insert into t_accManagerRoleDirAuth(");
            //    strSql.Append("FDirId,FAccessType,FGroupNo,FEntityNo,FProp,FLogID)");
            //    strSql.Append(" select distinct FDirId,FAccessType,FGroupNo,@FEntityNo as FEntityNo,FProp,@FLogID as FLogID from t_accManagerRoleDirAuth");
            //    strSql.Append(" where FGroupNo=@FGroupNo and FEntityNo=@FEntityNo");

            //    string strSqls = strSql.ToString();

            //    foreach (string gpID in dirUserList)
            //    {
            //        cmds = new CommandInfo();

            //        SqlParameter[] parameters2 = {
            //        new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
            //        new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15),
            //        new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};

            //        parameters2[0].Value = gpID;
            //        parameters2[1].Value = userModel.FEntityNo;
            //        parameters2[2].Value = userModel.FLogID;

            //        cmds.CommandText = strSqls;
            //        cmds.Parameters = parameters2;
            //        cmdList.Add(cmds);
            //    }



            //}


            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
            }

        }
        /// <summary>
        /// 更新用户
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="userModel">用户信息</param>
        /// <param name="userGroupList">用户组信息</param>
        /// <returns>是否更新成功</returns>
        public string EditPrvUser(Model.PrvUser userModel, List<string> userGroupList)
        {
            string groupID = "0";
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("Update t_PrvUser set FUserName=@FUserName,FLastLoginTime=@FLastLoginTime,");
            strSql.Append("FLastLoginIPAddr=@FLastLoginIPAddr,FLoginCnt=@FLoginCnt,FProhibitLogin=@FProhibitLogin,FForeverAvailable=@FForeverAvailable,");
            strSql.Append("FAvailableDate=@FAvailableDate,FEmplNo=@FEmplNo,FSSOUid=@FSSOUid,FIsLookAllDaily=@FIsLookAllDaily,FIsInstruct=@FIsInstruct");
            strSql.Append(" where FEntityNo=@FEntityNo and FLogID=@FLogID");
            SqlParameter[] parameters = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUserName", SqlDbType.NVarChar,30),
					new SqlParameter("@FLastLoginTime", SqlDbType.DateTime),
					new SqlParameter("@FLastLoginIPAddr", SqlDbType.NVarChar,15),
					new SqlParameter("@FLoginCnt", SqlDbType.Int,4),
					new SqlParameter("@FProhibitLogin", SqlDbType.Int,4),
					new SqlParameter("@FForeverAvailable", SqlDbType.Int,4),
					new SqlParameter("@FAvailableDate", SqlDbType.DateTime),
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FSSOUid", SqlDbType.NVarChar,50),
                    new SqlParameter("@FIsLookAllDaily",SqlDbType.NVarChar,1),
                    new SqlParameter("@FIsInstruct",SqlDbType.NVarChar,1)
                                        };
            parameters[0].Value = userModel.FLogID;
            parameters[1].Value = userModel.FEntityNo;
            parameters[2].Value = userModel.FUserName;
            parameters[3].Value = userModel.FLastLoginTime;
            parameters[4].Value = userModel.FLastLoginIPAddr;
            parameters[5].Value = userModel.FLoginCnt;
            parameters[6].Value = userModel.FProhibitLogin;
            parameters[7].Value = userModel.FForeverAvailable;
            parameters[8].Value = userModel.FAvailableDate;
            parameters[9].Value = userModel.FEmplNo;
            parameters[10].Value = userModel.FSSOUid;
            parameters[11].Value = userModel.FIsLookAllDaily;
            parameters[12].Value = userModel.FIsInstruct;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);
            //删除用户隶属组信息
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_PrvUserGroup WHERE FEntityNo=@FEntityNo and FLogID=@FLogID");
            SqlParameter[] parameters1 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12)
                                        };
            parameters1[0].Value = userModel.FLogID;
            parameters1[1].Value = userModel.FEntityNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters1;
            cmdList.Add(cmds);
            //增加用户隶属组信息和相关目录权限
            if (userGroupList.Count > 0)
            {
                foreach (string gpID in userGroupList)
                {
                    strSql = new StringBuilder();
                    cmds = new CommandInfo();
                    strSql.Append("INSERT INTO t_PrvUserGroup (FGroupNo,FEntityNo,FLogID) VALUES (@FGroupNo,@FEntityNo,@FLogID)");
                    SqlParameter[] parameters2 = {
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,18),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
					new SqlParameter("@FLogID", SqlDbType.NVarChar,30)
                                              };
                    parameters2[0].Value = gpID;
                    parameters2[1].Value = userModel.FEntityNo;
                    parameters2[2].Value = userModel.FLogID;
                    cmds.CommandText = strSql.ToString();
                    cmds.Parameters = parameters2;
                    cmdList.Add(cmds);
                }
            }



            //删除用户目录权限
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_accManagerRoleDirAuth WHERE FEntityNo=@FEntityNo and FLogID=@FLogID");
            SqlParameter[] parameters0 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,15)
                                        };
            parameters0[0].Value = userModel.FLogID;
            parameters0[1].Value = userModel.FEntityNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters0;
            cmdList.Add(cmds);


            //删除目录用户组信息
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_DirPrvUserGroup WHERE FEntityNo=@FEntityNo and FLogID=@FLogID");
            SqlParameter[] parameters3 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12)
                                        };
            parameters3[0].Value = userModel.FLogID;
            parameters3[1].Value = userModel.FEntityNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters3;
            cmdList.Add(cmds);
            //增加目录用户组信息
            //if (dirUserList.Count > 0)
            //{
            //    foreach (string gpID in dirUserList)
            //    {
            //        strSql = new StringBuilder();
            //        cmds = new CommandInfo();
            //        strSql.Append("INSERT INTO t_DirPrvUserGroup (FGroupNo,FEntityNo,FLogID) VALUES (@FGroupNo,@FEntityNo,@FLogID)");
            //        SqlParameter[] parameters4 = {
            //        new SqlParameter("@FGroupNo", SqlDbType.NVarChar,18),
            //        new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
            //        new SqlParameter("@FLogID", SqlDbType.NVarChar,30)
            //                                  };
            //        parameters4[0].Value = gpID;
            //        parameters4[1].Value = userModel.FEntityNo;
            //        parameters4[2].Value = userModel.FLogID;
            //        cmds.CommandText = strSql.ToString();
            //        cmds.Parameters = parameters4;
            //        cmdList.Add(cmds);
            //    }


            //    //增加用户目录权限,根据用户所属的目录用户组
            //    strSql = new StringBuilder();

            //    strSql.Append("insert into t_accManagerRoleDirAuth(");
            //    strSql.Append("FDirId,FAccessType,FGroupNo,FEntityNo,FProp,FLogID)");
            //    strSql.Append(" select distinct FDirId,FAccessType,FGroupNo,@FEntityNo as FEntityNo,FProp,@FLogID as FLogID from t_accManagerRoleDirAuth");
            //    strSql.Append(" where FGroupNo=@FGroupNo and FEntityNo=@FEntityNo");

            //    string strSqls = strSql.ToString();

            //    foreach (string gpID in dirUserList)
            //    {
            //        cmds = new CommandInfo();

            //        SqlParameter[] parameters00 = {
            //        new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
            //        new SqlParameter("@FEntityNo", SqlDbType.NVarChar,15),
            //        new SqlParameter("@FLogID", SqlDbType.NVarChar,30)};

            //        parameters00[0].Value = gpID;
            //        parameters00[1].Value = userModel.FEntityNo;
            //        parameters00[2].Value = userModel.FLogID;

            //        cmds.CommandText = strSqls;
            //        cmds.Parameters = parameters00;
            //        cmdList.Add(cmds);
            //    }



            //}
            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
            }
            //groupID = userModel.FGroupNo;
            return groupID;
        }


        /// <summary>
        /// 获取数据和部门编号 FLogID,FUserName,FDeptNo
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>用户信息列表</returns>
        public DataSet GetListDept(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FLogID,FUserName,FDeptNo from v_PrvUser");
            strSql.Append(" WHERE FEntityNo=@FEntityNo AND FIsMainPosition='Y'");
            strSql.Append(" ORDER BY FSeq,FDutyNo");
            SqlParameter[] parameters = {
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15) };
            parameters[0].Value = entityNo;

            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体,根据用户登陆号、密码
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="loginID">用户登录号</param>
        /// <param name="password">用户密码</param>
        /// <param name="entityNo">组织机构代码</param>
        /// <returns>用户信息</returns>
        public Model.PrvUser GetModelByLoginIDAndPassword(string loginID, string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT top 1 FLogID,FUserName ,Fpassword,FLastLoginTime ,FLastLoginIPAddr,FLoginCnt,FProhibitLogin,FForeverAvailable,FAvailableDate,FEmplNo,FEntityNo from t_PrvUser ");
            strSql.Append(" WHERE FLogID=@FLogID AND Fpassword=@FPassword");
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.VarChar,50),
                    new SqlParameter("@FPassword", SqlDbType.VarChar,50)};

            parameters[0].Value = loginID;
            parameters[1].Value = password;

            Model.PrvUser model = new Model.PrvUser();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.FLogID = ds.Tables[0].Rows[0]["FLogID"].ToString();
                model.FUserName = ds.Tables[0].Rows[0]["FUserName"].ToString();
                model.Fpassword = ds.Tables[0].Rows[0]["Fpassword"].ToString();
                if (ds.Tables[0].Rows[0]["FProhibitLogin"].ToString() != "")
                {
                    model.FProhibitLogin = int.Parse(ds.Tables[0].Rows[0]["FProhibitLogin"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FLoginCnt"].ToString() != "")
                {
                    model.FLoginCnt = int.Parse(ds.Tables[0].Rows[0]["FLoginCnt"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FAvailableDate"].ToString() != "")
                {
                    model.FAvailableDate = DateTime.Parse(ds.Tables[0].Rows[0]["FAvailableDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["FForeverAvailable"].ToString() != "")
                {
                    model.FForeverAvailable = int.Parse(ds.Tables[0].Rows[0]["FForeverAvailable"].ToString());
                }
                model.FLastLoginIPAddr = ds.Tables[0].Rows[0]["FLastLoginIPAddr"].ToString();
                if (ds.Tables[0].Rows[0]["FLastLoginTime"].ToString() != "")
                {
                    model.FLastLoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["FLastLoginTime"].ToString());
                }
                if (ds.Tables[0].Columns.Contains("FEmplNo"))
                {
                    model.FEmplNo = ds.Tables[0].Rows[0]["FEmplNo"].ToString();
                }
                if (ds.Tables[0].Columns.Contains("FEntityNo"))
                {
                    model.FEntityNo = ds.Tables[0].Rows[0]["FEntityNo"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="strWhere">SQL查询条件</param>
        /// <returns>返回用户信息列表</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FLogID,FEntityNo,FUserName,Fpassword,FLastLoginTime,FLastLoginIPAddr,FLoginCnt,FProhibitLogin,FForeverAvailable,FAvailableDate,FEmplNo,FSSOUid ");
            strSql.Append(" FROM t_PrvUser ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 更新用户登录信息
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="loginID">用户登录编号</param>
        /// <param name="lastLoginIpAddr">最后登录IP</param>
        public void UpdateUserLoginMsg(string loginID, string lastLoginIpAddr, string emplNo, string ipAddress)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("UPDATE t_PrvUser SET FLastLoginTime=GETDATE(),");
            strSql.Append("FLastLoginIPAddr=@FLastLoginIPAddr,");
            strSql.Append("FLoginCnt=FLoginCnt+1");
            strSql.Append(" WHERE FLogID=@FLogID");
            SqlParameter[] parameters = {
				    new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
					new SqlParameter("@FLastLoginIPAddr", SqlDbType.NVarChar,15)
                                        };
            parameters[0].Value = loginID;
            parameters[1].Value = lastLoginIpAddr;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //更新系统信息最近登录相关信息
            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("UPDATE t_Entity SET FLastLoginIPAddr=@FLastLoginIPAddr,");
            strSql.Append("FLogID=@FLogID,");
            strSql.Append("FAccessDate=GETDATE()");
            SqlParameter[] parameters1 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
                    new SqlParameter("@FLastLoginIPAddr", SqlDbType.NVarChar,15)
                                           };
            parameters1[0].Value = loginID;
            parameters1[1].Value = lastLoginIpAddr;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters1;
            cmdList.Add(cmds);

            //更新员工表的登录绑定IP
            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("UPDATE t_HREmpl SET FLoginIPAddress=@FLoginIPAddress");
            strSql.Append(" WHERE FEmplNo=@FEmplNo AND (FLoginIPAddress IS NULL OR FLoginIPAddress='')");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@FLoginIPAddress", SqlDbType.NVarChar,16),
                    new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15)
                                           };
            parameters2[0].Value = ipAddress;
            parameters2[1].Value = emplNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);


            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTran(cmdList);
            }
        }


        /// <summary>
        /// 修改密码
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="loginID">用户登录编号</param>
        /// <param name="password">用户登录密码</param>
        /// <param name="entityNo">组织机构编号</param>
        public void EditPassword(string logid, string password, string entityno)
        {
            string strSql = "update t_PrvUser set Fpassword=@Fpassword where FLogID=@FLogID and FEntityNo=@FEntityNo";
            SqlParameter[] parameters = {
					new SqlParameter("@FLogID", SqlDbType.VarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.VarChar,50),
                    new SqlParameter("@Fpassword", SqlDbType.VarChar,50)};

            parameters[0].Value = logid;
            parameters[1].Value = entityno;
            parameters[2].Value = password;

            DbHelperSQL.ExecuteSql(strSql, parameters);

        }
        /// <summary>
        /// 根据组织编号获取用户列表
        /// <creator>wangl</creator>
        /// <date>2012-06-11</date>
        /// </summary>
        /// <param name="orgNo"></param>
        /// <returns></returns>
        public DataSet GetPrvUserListByDeptNo(string deptNo,string entityNo)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select FLogID,FUserName,FDeptNo,FSeq from v_PrvUser ");
            strSql.Append(" WHERE FEntityNo=@FEntityNo AND FDeptNo=@FDeptNo");
            strSql.Append(" AND FIsMainPosition='Y'");
            SqlParameter[] parameters = { 
                    new SqlParameter("@FDeptNo", SqlDbType.NVarChar, 15), 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15)};

            parameters[0].Value = deptNo;
            parameters[1].Value = entityNo;

            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据组织编号获取本部门及其子部门的用户列表
        /// <creator>sunlj</creator>
        /// <date>2015-01-30</date>
        /// </summary>
        /// <param name="orgNo"></param>
        /// <returns></returns>
        public DataSet GetPrvUserListByDeptNoCTE(string deptNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"WITH cte AS 
(
SELECT  FEntityNo ,
        FDeptNo ,
        FDeptName ,
        FIsAgency ,
        FSeq ,
        FParentDeptNo
        FROM dbo.t_HRDept WHERE FDeptNo=@FDeptNo AND FEntityNo=@FEntityNo
UNION ALL
SELECT	a.FEntityNo ,
		a.FDeptNo ,
		a.FDeptName ,
		a.FIsAgency ,
		a.FSeq ,
		a.FParentDeptNo FROM dbo.t_HRDept a, cte b WHERE a.FParentDeptNo=b.FDeptNo AND a.FEntityNo=@FEntityNo
)
select FLogID,FUserName,FDeptNo,FSeq from v_PrvUser 
WHERE FEntityNo=@FEntityNo AND FDeptNo IN (SELECT FDeptNo FROM cte)
AND FIsMainPosition='Y'");

            SqlParameter[] parameters = { 
                    new SqlParameter("@FDeptNo", SqlDbType.NVarChar, 15), 
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar, 15)};

            parameters[0].Value = deptNo;
            parameters[1].Value = entityNo;

            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }




    }
}
